{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 08: SparkSQL\n",
    "\n",
    "Originally, SparkSQL was an extension of RDDs with the concept of a `DataFrame` that adds a \"schema\" for records, defined using Scala _case classes_, tuples, or a built-in schema mechanism. The DataFrame API is inspired by similar `DataFrame` concepts in R and Python libraries. The transformation and action steps written in any of the support languages, as well as SQL queries embedded in strings, are translated to the same, performant query execution model, optimized by a new query engine called *Catalyst*.\n",
    "\n",
    "The even newer `Dataset` API encapsulates `DataFrame`, but adds more type safety for the data columns. We'll stick with the `DataFrame` here.\n",
    "\n",
    "> **Tip:** Even if you prefer the Scala collections-like `RDD` API, I recommend using the `DataFrame` API, because the performance is _significantly_ better in most cases, due to internal optimizations.\n",
    "\n",
    "Furthermore, SparkSQL has convenient support for reading and writing files encoded using [Parquet](http://parquet.io), [ORC](https://orc.apache.org/), JSON, CSV, etc.\n",
    "\n",
    "Finally, SparkSQL embeds access to a Hive _metastore_, so you can create and delete tables, and run queries against them using SparkSQL.\n",
    "\n",
    "This example treats the KJV text we've been using as a table with a schema. It runs several SQL queries on the data, then performs the same calculation using the `DataFrame` API.\n",
    "\n",
    "See the corresponding Spark job [SparkSQL8.scala](https://github.com/deanwampler/spark-scala-tutorial/blob/master/src/main/scala/sparktutorial/SparkSQL8.scala) and the \"script\" suitable for _spark-shell_, [SparkSQL8-script.scala](https://github.com/deanwampler/spark-scala-tutorial/blob/master/src/main/scala/sparktutorial/SparkSQL8-script.scala), because SQL queries are nice to use interactively!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "in = ../data/kjvdat.txt\n",
       "abbrevToNames = ../data/abbrevs-to-names.tsv\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "../data/abbrevs-to-names.tsv"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "val in = \"../data/kjvdat.txt\"                       // '|' separated\n",
    "val abbrevToNames = \"../data/abbrevs-to-names.tsv\"  // tab separated"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This time, we won't use the `toText` method we defined before, we'll use a big regex and do pattern matching with it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "lineRE = ^\\s*([^|]+)\\s*\\|\\s*([\\d]+)\\s*\\|\\s*([\\d]+)\\s*\\|\\s*(.*)~?\\s*$\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "^\\s*([^|]+)\\s*\\|\\s*([\\d]+)\\s*\\|\\s*([\\d]+)\\s*\\|\\s*(.*)~?\\s*$"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "val lineRE = \"\"\"^\\s*([^|]+)\\s*\\|\\s*([\\d]+)\\s*\\|\\s*([\\d]+)\\s*\\|\\s*(.*)~?\\s*$\"\"\".r"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's define a case class `Verse` to represent our records."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "defined class Verse\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "case class Verse(book: String, chapter: Int, verse: Int, text: String)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now load and parse the data. Note that using `flatMap` effective removes the bad records, for which we return `Nil`, while we return `Seq(verse)` on success."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "versesRDD = MapPartitionsRDD[2] at flatMap at <console>:33\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "MapPartitionsRDD[2] at flatMap at <console>:33"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "val versesRDD = sc.textFile(in).flatMap {\n",
    "  case lineRE(book, chapter, verse, text) =>\n",
    "    Seq(Verse(book, chapter.toInt, verse.toInt, text))\n",
    "  case line =>\n",
    "    Console.err.println(s\"Unexpected line: $line\")\n",
    "    Nil // or use Seq.empty[Verse]. It will be eliminated by flattening.\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now create a `DataFrame` from this `RDD` and create a temporary \"view\". We're going to reuse this data over and over, so now it's very useful to _cache_ it in memory."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "verses = [book: string, chapter: int ... 2 more fields]\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "[book: string, chapter: int ... 2 more fields]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "val verses = spark.createDataFrame(versesRDD)\n",
    "verses.createOrReplaceTempView(\"kjv_bible\")\n",
    "verses.cache()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Print the first 20 lines (the default), or pass an integer argument to show a different number\n",
    "of lines, as here:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----+-------+-----+--------------------+\n",
      "|book|chapter|verse|                text|\n",
      "+----+-------+-----+--------------------+\n",
      "| Gen|      1|    1|In the beginning ...|\n",
      "| Gen|      1|    2|And the earth was...|\n",
      "| Gen|      1|    3|And God said, Let...|\n",
      "| Gen|      1|    4|And God saw the l...|\n",
      "| Gen|      1|    5|And God called th...|\n",
      "| Gen|      1|    6|And God said, Let...|\n",
      "| Gen|      1|    7|And God made the ...|\n",
      "| Gen|      1|    8|And God called th...|\n",
      "| Gen|      1|    9|And God said, Let...|\n",
      "| Gen|      1|   10|And God called th...|\n",
      "+----+-------+-----+--------------------+\n",
      "only showing top 10 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "verses.show(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Pass an optional `truncate = false` argument for wider output."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----+-------+-----+-----------------------------------------------------------------------------------------------------------------------------------------------+\n",
      "|book|chapter|verse|text                                                                                                                                           |\n",
      "+----+-------+-----+-----------------------------------------------------------------------------------------------------------------------------------------------+\n",
      "|Gen |1      |1    |In the beginning God created the heaven and the earth.~                                                                                        |\n",
      "|Gen |1      |2    |And the earth was without form, and void; and darkness was upon the face of the deep. And the Spirit of God moved upon the face of the waters.~|\n",
      "|Gen |1      |3    |And God said, Let there be light: and there was light.~                                                                                        |\n",
      "|Gen |1      |4    |And God saw the light, that it was good: and God divided the light from the darkness.~                                                         |\n",
      "|Gen |1      |5    |And God called the light Day, and the darkness he called Night. And the evening and the morning were the first day.~                           |\n",
      "+----+-------+-----+-----------------------------------------------------------------------------------------------------------------------------------------------+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "verses.show(5, truncate = false)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now write some SQL queries, with SQL and with the Dataset/DataFrame API!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "godVerses = [book: string, chapter: int ... 2 more fields]\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "[book: string, chapter: int ... 2 more fields]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "val godVerses = spark.sql(\"SELECT * FROM kjv_bible WHERE text LIKE '%God%'\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of verses that mention God: 3585\n",
      "+----+-------+-----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+\n",
      "|book|chapter|verse|text                                                                                                                                                                                                  |\n",
      "+----+-------+-----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+\n",
      "|Gen |1      |1    |In the beginning God created the heaven and the earth.~                                                                                                                                               |\n",
      "|Gen |1      |2    |And the earth was without form, and void; and darkness was upon the face of the deep. And the Spirit of God moved upon the face of the waters.~                                                       |\n",
      "|Gen |1      |3    |And God said, Let there be light: and there was light.~                                                                                                                                               |\n",
      "|Gen |1      |4    |And God saw the light, that it was good: and God divided the light from the darkness.~                                                                                                                |\n",
      "|Gen |1      |5    |And God called the light Day, and the darkness he called Night. And the evening and the morning were the first day.~                                                                                  |\n",
      "|Gen |1      |6    |And God said, Let there be a firmament in the midst of the waters, and let it divide the waters from the waters.~                                                                                     |\n",
      "|Gen |1      |7    |And God made the firmament, and divided the waters which were under the firmament from the waters which were above the firmament: and it was so.~                                                     |\n",
      "|Gen |1      |8    |And God called the firmament Heaven. And the evening and the morning were the second day.~                                                                                                            |\n",
      "|Gen |1      |9    |And God said, Let the waters under the heaven be gathered together unto one place, and let the dry land appear: and it was so.~                                                                       |\n",
      "|Gen |1      |10   |And God called the dry land Earth; and the gathering together of the waters called he Seas: and God saw that it was good.~                                                                            |\n",
      "|Gen |1      |11   |And God said, Let the earth bring forth grass, the herb yielding seed, and the fruit tree yielding fruit after his kind, whose seed is in itself, upon the earth: and it was so.~                     |\n",
      "|Gen |1      |12   |And the earth brought forth grass, and herb yielding seed after his kind, and the tree yielding fruit, whose seed was in itself, after his kind: and God saw that it was good.~                       |\n",
      "|Gen |1      |14   |And God said, Let there be lights in the firmament of the heaven to divide the day from the night; and let them be for signs, and for seasons, and for days, and years:~                              |\n",
      "|Gen |1      |16   |And God made two great lights; the greater light to rule the day, and the lesser light to rule the night: he made the stars also.~                                                                    |\n",
      "|Gen |1      |17   |And God set them in the firmament of the heaven to give light upon the earth,~                                                                                                                        |\n",
      "|Gen |1      |18   |And to rule over the day and over the night, and to divide the light from the darkness: and God saw that it was good.~                                                                                |\n",
      "|Gen |1      |20   |And God said, Let the waters bring forth abundantly the moving creature that hath life, and fowl that may fly above the earth in the open firmament of heaven.~                                       |\n",
      "|Gen |1      |21   |And God created great whales, and every living creature that moveth, which the waters brought forth abundantly, after their kind, and every winged fowl after his kind: and God saw that it was good.~|\n",
      "|Gen |1      |22   |And God blessed them, saying, Be fruitful, and multiply, and fill the waters in the seas, and let fowl multiply in the earth.~                                                                        |\n",
      "|Gen |1      |24   |And God said, Let the earth bring forth the living creature after his kind, cattle, and creeping thing, and beast of the earth after his kind: and it was so.~                                        |\n",
      "+----+-------+-----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "println(\"Number of verses that mention God: \"+godVerses.count())\n",
    "godVerses.show(truncate=false)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The query plan:\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "      +- SerializeFromObject [staticinvoke(class org.apache.spark.unsafe.types.UTF8String, StringType, fromString, assertnotnull(assertnotnull(input[0, Verse, true])).book, true, false) AS book#5, assertnotnull(assertnotnull(input[0, Verse, true])).chapter AS chapter#6, assertnotnull(assertnotnull(input[0, Verse, true])).verse AS verse#7, staticinvoke(class org.apache.spark.unsafe.types.UTF8String, StringType, fromString, assertnotnull(assertnotnul...\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "== Parsed Logical Plan ==\n",
       "'Project [*]\n",
       "+- 'Filter 'text LIKE %God%\n",
       "   +- 'UnresolvedRelation `kjv_bible`\n",
       "\n",
       "== Analyzed Logical Plan ==\n",
       "book: string, chapter: int, verse: int, text: string\n",
       "Project [book#5, chapter#6, verse#7, text#8]\n",
       "+- Filter text#8 LIKE %God%\n",
       "   +- SubqueryAlias kjv_bible\n",
       "      +- SerializeFromObject [staticinvoke(class org.apache.spark.unsafe.types.UTF8String, StringType, fromString, assertnotnull(assertnotnull(input[0, $line23.$read$$iw$$iw$Verse, true])).book, true, false) AS book#5, assertnotnull(assertnotnull(input[0, $line23.$read$$iw$$iw$Verse, true])).chapter AS chapter#6, assertnotnull(assertnotnull(input[0, $line23.$read$$iw$$iw$Verse, true])).verse AS verse#7, staticinvoke(class org.apache.spark.unsafe.types.UTF8String, StringType, fromString, assertnotnull(assertnotnull(input[0, $line23.$read$$iw$$iw$Verse, true])).text, true, false) AS text#8]\n",
       "         +- ExternalRDD [obj#4]\n",
       "\n",
       "== Optimized Logical Plan ==\n",
       "Filter (isnotnull(text#8) && Contains(text#8, God))\n",
       "+- InMemoryRelation [book#5, chapter#6, verse#7, text#8], true, 10000, StorageLevel(disk, memory, deserialized, 1 replicas)\n",
       "      +- *(1) SerializeFromObject [staticinvoke(class org.apache.spark.unsafe.types.UTF8String, StringType, fromString, assertnotnull(input[0, $line23.$read$$iw$$iw$Verse, true]).book, true, false) AS book#5, assertnotnull(input[0, $line23.$read$$iw$$iw$Verse, true]).chapter AS chapter#6, assertnotnull(input[0, $line23.$read$$iw$$iw$Verse, true]).verse AS verse#7, staticinvoke(class org.apache.spark.unsafe.types.UTF8String, StringType, fromString, assertnotnull(input[0, $line23.$read$$iw$$iw$Verse, true]).text, true, false) AS text#8]\n",
       "         +- Scan ExternalRDDScan[obj#4]\n",
       "\n",
       "== Physical Plan ==\n",
       "*(1) Filter (isnotnull(text#8) && Contains(text#8, God))\n",
       "+- InMemoryTableScan [book#5, chapter#6, verse#7, text#8], [isnotnull(text#8), Contains(text#8, God)]\n",
       "      +- InMemoryRelation [book#5, chapter#6, verse#7, text#8], true, 10000, StorageLevel(disk, memory, deserialized, 1 replicas)\n",
       "            +- *(1) SerializeFromObject [staticinvoke(class org.apache.spark.unsafe.types.UTF8String, StringType, fromString, assertnotnull(input[0, $line23.$read$$iw$$iw$Verse, true]).book, true, false) AS book#5, assertnotnull(input[0, $line23.$read$$iw$$iw$Verse, true]).chapter AS chapter#6, assertnotnull(input[0, $line23.$read$$iw$$iw$Verse, true]).verse AS verse#7, staticinvoke(class org.apache.spark.unsafe.types.UTF8String, StringType, fromString, assertnotnull(input[0, $line23.$read$$iw$$iw$Verse, true]).text, true, false) AS text#8]\n",
       "               +- Scan ExternalRDDScan[obj#4]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "println(\"The query plan:\")\n",
    "godVerses.queryExecution   // Compare with godVerses.explain(true)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now using the API:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of verses that mention God: 3585\n",
      "+----+-------+-----+--------------------+\n",
      "|book|chapter|verse|                text|\n",
      "+----+-------+-----+--------------------+\n",
      "| Gen|      1|    1|In the beginning ...|\n",
      "| Gen|      1|    2|And the earth was...|\n",
      "| Gen|      1|    3|And God said, Let...|\n",
      "| Gen|      1|    4|And God saw the l...|\n",
      "| Gen|      1|    5|And God called th...|\n",
      "| Gen|      1|    6|And God said, Let...|\n",
      "| Gen|      1|    7|And God made the ...|\n",
      "| Gen|      1|    8|And God called th...|\n",
      "| Gen|      1|    9|And God said, Let...|\n",
      "| Gen|      1|   10|And God called th...|\n",
      "| Gen|      1|   11|And God said, Let...|\n",
      "| Gen|      1|   12|And the earth bro...|\n",
      "| Gen|      1|   14|And God said, Let...|\n",
      "| Gen|      1|   16|And God made two ...|\n",
      "| Gen|      1|   17|And God set them ...|\n",
      "| Gen|      1|   18|And to rule over ...|\n",
      "| Gen|      1|   20|And God said, Let...|\n",
      "| Gen|      1|   21|And God created g...|\n",
      "| Gen|      1|   22|And God blessed t...|\n",
      "| Gen|      1|   24|And God said, Let...|\n",
      "+----+-------+-----+--------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "godVersesDF = [book: string, chapter: int ... 2 more fields]\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "[book: string, chapter: int ... 2 more fields]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "val godVersesDF = verses.filter(verses(\"text\").contains(\"God\"))\n",
    "println(\"Number of verses that mention God: \"+godVersesDF.count())\n",
    "godVersesDF.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Use `GROUP BY` and column aliasing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----+-----+\n",
      "|book|count|\n",
      "+----+-----+\n",
      "| Sa1|  810|\n",
      "| Ecc|  222|\n",
      "| Mic|  105|\n",
      "| Ti2|   83|\n",
      "| Job| 1070|\n",
      "| Th1|   89|\n",
      "| Jer| 1364|\n",
      "| Ezr|  280|\n",
      "| Phi|  104|\n",
      "| Rut|   85|\n",
      "| Joe|   73|\n",
      "| Gal|  149|\n",
      "| Th2|   47|\n",
      "| Jo2|   13|\n",
      "| Mal|   55|\n",
      "| Luk| 1151|\n",
      "| Pro|  915|\n",
      "| Jdg|  618|\n",
      "| Nah|   47|\n",
      "| Jde|   25|\n",
      "| Heb|  303|\n",
      "| Mat| 1071|\n",
      "| Kg1|  816|\n",
      "| Kg2|  719|\n",
      "| Col|   95|\n",
      "| Ti1|  113|\n",
      "| Neh|  406|\n",
      "| Rev|  404|\n",
      "| Sol|  117|\n",
      "| Ch2|  822|\n",
      "| Num| 1288|\n",
      "| Jam|  108|\n",
      "| Hos|  197|\n",
      "| Jo1|  105|\n",
      "| Rom|  433|\n",
      "| Deu|  959|\n",
      "| Gen| 1533|\n",
      "| Pe1|  105|\n",
      "| Ch1|  942|\n",
      "| Joh|  879|\n",
      "| Psa| 2461|\n",
      "| Jos|  658|\n",
      "| Hab|   56|\n",
      "| Dan|  357|\n",
      "| Plm|   25|\n",
      "| Est|  167|\n",
      "| Sa2|  695|\n",
      "| Pe2|   61|\n",
      "| Mar|  678|\n",
      "| Hag|   38|\n",
      "| Eph|  155|\n",
      "| Exo| 1213|\n",
      "| Co2|  257|\n",
      "| Lam|  154|\n",
      "| Tit|   46|\n",
      "| Amo|  146|\n",
      "| Jo3|   14|\n",
      "| Zac|  211|\n",
      "| Co1|  437|\n",
      "| Isa| 1292|\n",
      "| Zep|   53|\n",
      "| Lev|  859|\n",
      "| Oba|   21|\n",
      "| Act| 1007|\n",
      "| Eze| 1273|\n",
      "| Jon|   48|\n",
      "+----+-----+\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "counts = [book: string, count: bigint]\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "[book: string, count: bigint]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "val counts = spark.sql(\"SELECT book, COUNT(*) as count FROM kjv_bible GROUP BY book\")\n",
    "counts.show(100)  // print the 1st 100 lines, but there are only 66 books/records..."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Exercise**: Update the previous query to sort output by the book names, by the counts. For convenience, I've pasted in the same query (different variable name). How much overhead does this add?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "val sorted_counts = spark.sql(\"SELECT book, COUNT(*) as count FROM kjv_bible GROUP BY book\")\n",
    "sorted_counts.show(100)  // print the 1st 100 lines, but there are only 66 books/records..."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Use `coalesce` when you have too many partitions, e.g., a small data set and the default number of partitions (200) is too large."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "counts.count (can take a while, # partitions = 200):\n",
      "result: 66\n",
      "counts1.count (usually faster, # partitions = 1):\n",
      "result: 66\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "counts1 = [book: string, count: bigint]\n",
       "nPartitions = 200\n",
       "nPartitions1 = 1\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "1"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "val counts1 = counts.coalesce(1)\n",
    "val nPartitions  = counts.rdd.partitions.size\n",
    "val nPartitions1 = counts1.rdd.partitions.size\n",
    "println(s\"counts.count (can take a while, # partitions = $nPartitions):\")\n",
    "println(s\"result: ${counts.count}\")\n",
    "println(s\"counts1.count (usually faster, # partitions = $nPartitions1):\")\n",
    "println(s\"result: ${counts1.count}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now do `GROUP BY` with the DataFrame API."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----+-----+\n",
      "|book|count|\n",
      "+----+-----+\n",
      "| Sa1|  810|\n",
      "| Ecc|  222|\n",
      "| Mic|  105|\n",
      "| Ti2|   83|\n",
      "| Job| 1070|\n",
      "| Th1|   89|\n",
      "| Jer| 1364|\n",
      "| Ezr|  280|\n",
      "| Phi|  104|\n",
      "| Rut|   85|\n",
      "| Joe|   73|\n",
      "| Gal|  149|\n",
      "| Th2|   47|\n",
      "| Jo2|   13|\n",
      "| Mal|   55|\n",
      "| Luk| 1151|\n",
      "| Pro|  915|\n",
      "| Jdg|  618|\n",
      "| Nah|   47|\n",
      "| Jde|   25|\n",
      "+----+-----+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "countsDF = [book: string, count: bigint]\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "66"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "val countsDF = verses.groupBy(\"book\").count()\n",
    "countsDF.show(20)\n",
    "countsDF.count"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Exercise**: Add sorting by book names and by the counts, using the API. For convenience, I've pasted in the same query (different variable name)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "val countsDF = verses.groupBy(\"book\").count()\n",
    "countsDF.show(20)\n",
    "countsDF.count"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Aggregations, like in Data Warehousing. We need to import some functions first:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "import org.apache.spark.sql.functions._    // for min, max, etc."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----+------------+----------+--------+\n",
      "|book|max(chapter)|max(verse)|count(1)|\n",
      "+----+------------+----------+--------+\n",
      "| Psa|         150|       176|    2461|\n",
      "| Gen|          50|        67|    1533|\n",
      "| Jer|          52|        64|    1364|\n",
      "| Isa|          66|        38|    1292|\n",
      "| Num|          36|        89|    1288|\n",
      "| Eze|          48|        63|    1273|\n",
      "| Exo|          40|        51|    1213|\n",
      "| Luk|          24|        80|    1151|\n",
      "| Mat|          28|        75|    1071|\n",
      "| Job|          42|        41|    1070|\n",
      "| Act|          28|        60|    1007|\n",
      "| Deu|          34|        68|     959|\n",
      "| Ch1|          29|        81|     942|\n",
      "| Pro|          31|        36|     915|\n",
      "| Joh|          21|        71|     879|\n",
      "| Lev|          27|        59|     859|\n",
      "| Ch2|          36|        42|     822|\n",
      "| Kg1|          22|        66|     816|\n",
      "| Sa1|          31|        58|     810|\n",
      "| Kg2|          25|        44|     719|\n",
      "| Sa2|          24|        51|     695|\n",
      "| Mar|          16|        72|     678|\n",
      "| Jos|          24|        63|     658|\n",
      "| Jdg|          21|        57|     618|\n",
      "| Co1|          16|        58|     437|\n",
      "| Rom|          16|        39|     433|\n",
      "| Neh|          13|        73|     406|\n",
      "| Rev|          22|        29|     404|\n",
      "| Dan|          12|        49|     357|\n",
      "| Heb|          13|        40|     303|\n",
      "| Ezr|          10|        70|     280|\n",
      "| Co2|          13|        33|     257|\n",
      "| Ecc|          12|        29|     222|\n",
      "| Zac|          14|        23|     211|\n",
      "| Hos|          14|        23|     197|\n",
      "| Est|          10|        32|     167|\n",
      "| Eph|           6|        33|     155|\n",
      "| Lam|           5|        66|     154|\n",
      "| Gal|           6|        31|     149|\n",
      "| Amo|           9|        27|     146|\n",
      "| Sol|           8|        17|     117|\n",
      "| Ti1|           6|        25|     113|\n",
      "| Jam|           5|        27|     108|\n",
      "| Jo1|           5|        29|     105|\n",
      "| Mic|           7|        20|     105|\n",
      "| Pe1|           5|        25|     105|\n",
      "| Phi|           4|        30|     104|\n",
      "| Col|           4|        29|      95|\n",
      "| Th1|           5|        28|      89|\n",
      "| Rut|           4|        23|      85|\n",
      "| Ti2|           4|        26|      83|\n",
      "| Joe|           3|        32|      73|\n",
      "| Pe2|           3|        22|      61|\n",
      "| Hab|           3|        20|      56|\n",
      "| Mal|           4|        18|      55|\n",
      "| Zep|           3|        20|      53|\n",
      "| Jon|           4|        17|      48|\n",
      "| Nah|           3|        19|      47|\n",
      "| Th2|           3|        18|      47|\n",
      "| Tit|           3|        16|      46|\n",
      "| Hag|           2|        23|      38|\n",
      "| Jde|           1|        25|      25|\n",
      "| Plm|           1|        25|      25|\n",
      "| Oba|           1|        21|      21|\n",
      "| Jo3|           1|        14|      14|\n",
      "| Jo2|           1|        13|      13|\n",
      "+----+------------+----------+--------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "verses.groupBy(\"book\").agg(\n",
    "  max(verses(\"chapter\")),\n",
    "  max(verses(\"verse\")),\n",
    "  count(verses(\"*\"))\n",
    ").sort($\"count(1)\".desc, $\"book\").show(100)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Alternative way of referencing columns in verses."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----+------------+----------+--------+\n",
      "|book|max(chapter)|max(verse)|count(1)|\n",
      "+----+------------+----------+--------+\n",
      "| Psa|         150|       176|    2461|\n",
      "| Gen|          50|        67|    1533|\n",
      "| Jer|          52|        64|    1364|\n",
      "| Isa|          66|        38|    1292|\n",
      "| Num|          36|        89|    1288|\n",
      "| Eze|          48|        63|    1273|\n",
      "| Exo|          40|        51|    1213|\n",
      "| Luk|          24|        80|    1151|\n",
      "| Mat|          28|        75|    1071|\n",
      "| Job|          42|        41|    1070|\n",
      "| Act|          28|        60|    1007|\n",
      "| Deu|          34|        68|     959|\n",
      "| Ch1|          29|        81|     942|\n",
      "| Pro|          31|        36|     915|\n",
      "| Joh|          21|        71|     879|\n",
      "| Lev|          27|        59|     859|\n",
      "| Ch2|          36|        42|     822|\n",
      "| Kg1|          22|        66|     816|\n",
      "| Sa1|          31|        58|     810|\n",
      "| Kg2|          25|        44|     719|\n",
      "| Sa2|          24|        51|     695|\n",
      "| Mar|          16|        72|     678|\n",
      "| Jos|          24|        63|     658|\n",
      "| Jdg|          21|        57|     618|\n",
      "| Co1|          16|        58|     437|\n",
      "| Rom|          16|        39|     433|\n",
      "| Neh|          13|        73|     406|\n",
      "| Rev|          22|        29|     404|\n",
      "| Dan|          12|        49|     357|\n",
      "| Heb|          13|        40|     303|\n",
      "| Ezr|          10|        70|     280|\n",
      "| Co2|          13|        33|     257|\n",
      "| Ecc|          12|        29|     222|\n",
      "| Zac|          14|        23|     211|\n",
      "| Hos|          14|        23|     197|\n",
      "| Est|          10|        32|     167|\n",
      "| Eph|           6|        33|     155|\n",
      "| Lam|           5|        66|     154|\n",
      "| Gal|           6|        31|     149|\n",
      "| Amo|           9|        27|     146|\n",
      "| Sol|           8|        17|     117|\n",
      "| Ti1|           6|        25|     113|\n",
      "| Jam|           5|        27|     108|\n",
      "| Jo1|           5|        29|     105|\n",
      "| Mic|           7|        20|     105|\n",
      "| Pe1|           5|        25|     105|\n",
      "| Phi|           4|        30|     104|\n",
      "| Col|           4|        29|      95|\n",
      "| Th1|           5|        28|      89|\n",
      "| Rut|           4|        23|      85|\n",
      "| Ti2|           4|        26|      83|\n",
      "| Joe|           3|        32|      73|\n",
      "| Pe2|           3|        22|      61|\n",
      "| Hab|           3|        20|      56|\n",
      "| Mal|           4|        18|      55|\n",
      "| Zep|           3|        20|      53|\n",
      "| Jon|           4|        17|      48|\n",
      "| Nah|           3|        19|      47|\n",
      "| Th2|           3|        18|      47|\n",
      "| Tit|           3|        16|      46|\n",
      "| Hag|           2|        23|      38|\n",
      "| Jde|           1|        25|      25|\n",
      "| Plm|           1|        25|      25|\n",
      "| Oba|           1|        21|      21|\n",
      "| Jo3|           1|        14|      14|\n",
      "| Jo2|           1|        13|      13|\n",
      "+----+------------+----------+--------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "verses.groupBy(\"book\").agg(\n",
    "  max($\"chapter\"),\n",
    "  max($\"verse\"),\n",
    "  count($\"*\")\n",
    ").sort($\"count(1)\".desc, $\"book\").show(100)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "With just a single column, cube and rollup make less sense, but in a bigger dataset, you could do cubes and rollups, too."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----+------------+----------+--------+\n",
      "|book|max(chapter)|max(verse)|count(1)|\n",
      "+----+------------+----------+--------+\n",
      "|null|         150|       176|   31102|\n",
      "| Psa|         150|       176|    2461|\n",
      "| Gen|          50|        67|    1533|\n",
      "| Jer|          52|        64|    1364|\n",
      "| Isa|          66|        38|    1292|\n",
      "| Num|          36|        89|    1288|\n",
      "| Eze|          48|        63|    1273|\n",
      "| Exo|          40|        51|    1213|\n",
      "| Luk|          24|        80|    1151|\n",
      "| Mat|          28|        75|    1071|\n",
      "| Job|          42|        41|    1070|\n",
      "| Act|          28|        60|    1007|\n",
      "| Deu|          34|        68|     959|\n",
      "| Ch1|          29|        81|     942|\n",
      "| Pro|          31|        36|     915|\n",
      "| Joh|          21|        71|     879|\n",
      "| Lev|          27|        59|     859|\n",
      "| Ch2|          36|        42|     822|\n",
      "| Kg1|          22|        66|     816|\n",
      "| Sa1|          31|        58|     810|\n",
      "| Kg2|          25|        44|     719|\n",
      "| Sa2|          24|        51|     695|\n",
      "| Mar|          16|        72|     678|\n",
      "| Jos|          24|        63|     658|\n",
      "| Jdg|          21|        57|     618|\n",
      "| Co1|          16|        58|     437|\n",
      "| Rom|          16|        39|     433|\n",
      "| Neh|          13|        73|     406|\n",
      "| Rev|          22|        29|     404|\n",
      "| Dan|          12|        49|     357|\n",
      "| Heb|          13|        40|     303|\n",
      "| Ezr|          10|        70|     280|\n",
      "| Co2|          13|        33|     257|\n",
      "| Ecc|          12|        29|     222|\n",
      "| Zac|          14|        23|     211|\n",
      "| Hos|          14|        23|     197|\n",
      "| Est|          10|        32|     167|\n",
      "| Eph|           6|        33|     155|\n",
      "| Lam|           5|        66|     154|\n",
      "| Gal|           6|        31|     149|\n",
      "| Amo|           9|        27|     146|\n",
      "| Sol|           8|        17|     117|\n",
      "| Ti1|           6|        25|     113|\n",
      "| Jam|           5|        27|     108|\n",
      "| Jo1|           5|        29|     105|\n",
      "| Mic|           7|        20|     105|\n",
      "| Pe1|           5|        25|     105|\n",
      "| Phi|           4|        30|     104|\n",
      "| Col|           4|        29|      95|\n",
      "| Th1|           5|        28|      89|\n",
      "| Rut|           4|        23|      85|\n",
      "| Ti2|           4|        26|      83|\n",
      "| Joe|           3|        32|      73|\n",
      "| Pe2|           3|        22|      61|\n",
      "| Hab|           3|        20|      56|\n",
      "| Mal|           4|        18|      55|\n",
      "| Zep|           3|        20|      53|\n",
      "| Jon|           4|        17|      48|\n",
      "| Nah|           3|        19|      47|\n",
      "| Th2|           3|        18|      47|\n",
      "| Tit|           3|        16|      46|\n",
      "| Hag|           2|        23|      38|\n",
      "| Jde|           1|        25|      25|\n",
      "| Plm|           1|        25|      25|\n",
      "| Oba|           1|        21|      21|\n",
      "| Jo3|           1|        14|      14|\n",
      "| Jo2|           1|        13|      13|\n",
      "+----+------------+----------+--------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "verses.cube(\"book\").agg(\n",
    "  max($\"chapter\"),\n",
    "  max($\"verse\"),\n",
    "  count($\"*\")\n",
    ").sort($\"count(1)\".desc, $\"book\").show(100)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----+------------+----------+--------+\n",
      "|book|max(chapter)|max(verse)|count(1)|\n",
      "+----+------------+----------+--------+\n",
      "|null|         150|       176|   31102|\n",
      "| Psa|         150|       176|    2461|\n",
      "| Gen|          50|        67|    1533|\n",
      "| Jer|          52|        64|    1364|\n",
      "| Isa|          66|        38|    1292|\n",
      "| Num|          36|        89|    1288|\n",
      "| Eze|          48|        63|    1273|\n",
      "| Exo|          40|        51|    1213|\n",
      "| Luk|          24|        80|    1151|\n",
      "| Mat|          28|        75|    1071|\n",
      "| Job|          42|        41|    1070|\n",
      "| Act|          28|        60|    1007|\n",
      "| Deu|          34|        68|     959|\n",
      "| Ch1|          29|        81|     942|\n",
      "| Pro|          31|        36|     915|\n",
      "| Joh|          21|        71|     879|\n",
      "| Lev|          27|        59|     859|\n",
      "| Ch2|          36|        42|     822|\n",
      "| Kg1|          22|        66|     816|\n",
      "| Sa1|          31|        58|     810|\n",
      "| Kg2|          25|        44|     719|\n",
      "| Sa2|          24|        51|     695|\n",
      "| Mar|          16|        72|     678|\n",
      "| Jos|          24|        63|     658|\n",
      "| Jdg|          21|        57|     618|\n",
      "| Co1|          16|        58|     437|\n",
      "| Rom|          16|        39|     433|\n",
      "| Neh|          13|        73|     406|\n",
      "| Rev|          22|        29|     404|\n",
      "| Dan|          12|        49|     357|\n",
      "| Heb|          13|        40|     303|\n",
      "| Ezr|          10|        70|     280|\n",
      "| Co2|          13|        33|     257|\n",
      "| Ecc|          12|        29|     222|\n",
      "| Zac|          14|        23|     211|\n",
      "| Hos|          14|        23|     197|\n",
      "| Est|          10|        32|     167|\n",
      "| Eph|           6|        33|     155|\n",
      "| Lam|           5|        66|     154|\n",
      "| Gal|           6|        31|     149|\n",
      "| Amo|           9|        27|     146|\n",
      "| Sol|           8|        17|     117|\n",
      "| Ti1|           6|        25|     113|\n",
      "| Jam|           5|        27|     108|\n",
      "| Jo1|           5|        29|     105|\n",
      "| Mic|           7|        20|     105|\n",
      "| Pe1|           5|        25|     105|\n",
      "| Phi|           4|        30|     104|\n",
      "| Col|           4|        29|      95|\n",
      "| Th1|           5|        28|      89|\n",
      "| Rut|           4|        23|      85|\n",
      "| Ti2|           4|        26|      83|\n",
      "| Joe|           3|        32|      73|\n",
      "| Pe2|           3|        22|      61|\n",
      "| Hab|           3|        20|      56|\n",
      "| Mal|           4|        18|      55|\n",
      "| Zep|           3|        20|      53|\n",
      "| Jon|           4|        17|      48|\n",
      "| Nah|           3|        19|      47|\n",
      "| Th2|           3|        18|      47|\n",
      "| Tit|           3|        16|      46|\n",
      "| Hag|           2|        23|      38|\n",
      "| Jde|           1|        25|      25|\n",
      "| Plm|           1|        25|      25|\n",
      "| Oba|           1|        21|      21|\n",
      "| Jo3|           1|        14|      14|\n",
      "| Jo2|           1|        13|      13|\n",
      "+----+------------+----------+--------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "verses.rollup(\"book\").agg(\n",
    "  max($\"chapter\"),\n",
    "  max($\"verse\"),\n",
    "  count($\"*\")\n",
    ").sort($\"count(1)\".desc, $\"book\").show(100)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Map a field to a method to apply to it, but limited to at most one method per field."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----+------------+----------+--------+\n",
      "|book|max(chapter)|max(verse)|count(1)|\n",
      "+----+------------+----------+--------+\n",
      "|null|         150|       176|   31102|\n",
      "| Psa|         150|       176|    2461|\n",
      "| Gen|          50|        67|    1533|\n",
      "| Jer|          52|        64|    1364|\n",
      "| Isa|          66|        38|    1292|\n",
      "| Num|          36|        89|    1288|\n",
      "| Eze|          48|        63|    1273|\n",
      "| Exo|          40|        51|    1213|\n",
      "| Luk|          24|        80|    1151|\n",
      "| Mat|          28|        75|    1071|\n",
      "| Job|          42|        41|    1070|\n",
      "| Act|          28|        60|    1007|\n",
      "| Deu|          34|        68|     959|\n",
      "| Ch1|          29|        81|     942|\n",
      "| Pro|          31|        36|     915|\n",
      "| Joh|          21|        71|     879|\n",
      "| Lev|          27|        59|     859|\n",
      "| Ch2|          36|        42|     822|\n",
      "| Kg1|          22|        66|     816|\n",
      "| Sa1|          31|        58|     810|\n",
      "| Kg2|          25|        44|     719|\n",
      "| Sa2|          24|        51|     695|\n",
      "| Mar|          16|        72|     678|\n",
      "| Jos|          24|        63|     658|\n",
      "| Jdg|          21|        57|     618|\n",
      "| Co1|          16|        58|     437|\n",
      "| Rom|          16|        39|     433|\n",
      "| Neh|          13|        73|     406|\n",
      "| Rev|          22|        29|     404|\n",
      "| Dan|          12|        49|     357|\n",
      "| Heb|          13|        40|     303|\n",
      "| Ezr|          10|        70|     280|\n",
      "| Co2|          13|        33|     257|\n",
      "| Ecc|          12|        29|     222|\n",
      "| Zac|          14|        23|     211|\n",
      "| Hos|          14|        23|     197|\n",
      "| Est|          10|        32|     167|\n",
      "| Eph|           6|        33|     155|\n",
      "| Lam|           5|        66|     154|\n",
      "| Gal|           6|        31|     149|\n",
      "| Amo|           9|        27|     146|\n",
      "| Sol|           8|        17|     117|\n",
      "| Ti1|           6|        25|     113|\n",
      "| Jam|           5|        27|     108|\n",
      "| Jo1|           5|        29|     105|\n",
      "| Mic|           7|        20|     105|\n",
      "| Pe1|           5|        25|     105|\n",
      "| Phi|           4|        30|     104|\n",
      "| Col|           4|        29|      95|\n",
      "| Th1|           5|        28|      89|\n",
      "| Rut|           4|        23|      85|\n",
      "| Ti2|           4|        26|      83|\n",
      "| Joe|           3|        32|      73|\n",
      "| Pe2|           3|        22|      61|\n",
      "| Hab|           3|        20|      56|\n",
      "| Mal|           4|        18|      55|\n",
      "| Zep|           3|        20|      53|\n",
      "| Jon|           4|        17|      48|\n",
      "| Nah|           3|        19|      47|\n",
      "| Th2|           3|        18|      47|\n",
      "| Tit|           3|        16|      46|\n",
      "| Hag|           2|        23|      38|\n",
      "| Jde|           1|        25|      25|\n",
      "| Plm|           1|        25|      25|\n",
      "| Oba|           1|        21|      21|\n",
      "| Jo3|           1|        14|      14|\n",
      "| Jo2|           1|        13|      13|\n",
      "+----+------------+----------+--------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "verses.rollup(\"book\").agg(Map(\n",
    "  \"chapter\" -> \"max\",\n",
    "  \"verse\" -> \"max\",\n",
    "  \"*\" -> \"count\"\n",
    ")).sort($\"count(1)\".desc, $\"book\").show(100)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exercises"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exercise 1: Try joins with the abbreviation data\n",
    "\n",
    "See the project solution file [SparkSQL8-join-with-abbreviations-script.scala](https://github.com/deanwampler/spark-scala-tutorial/blob/master/src/main/scala/sparktutorial/solns/SparkSQL8-join-with-abbreviations-script.scala).\n",
    "\n",
    "Here we set up the abbreviations, similar to the RDD Joins examples."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now load the abbreviations, similar to how we loaded the Bible verses. First we need a case class..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "defined class Abbrev\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "case class Abbrev(abbrev: String, name: String)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "abbrevNamesRDD = MapPartitionsRDD[164] at flatMap at <console>:34\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "MapPartitionsRDD[164] at flatMap at <console>:34"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "val abbrevNamesRDD = sc.textFile(abbrevToNames).flatMap { line =>\n",
    "  val ary=line.split(\"\\t\")\n",
    "  if (ary.length != 2) {\n",
    "    Console.err.println(s\"Unexpected line: $line\")\n",
    "    Nil // or use Seq.empty[Abbrev]. It will be eliminated by flattening.\n",
    "  } else {\n",
    "    Seq(Abbrev(ary(0), ary(1)))\n",
    "  }\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "abbrevNames = [abbrev: string, name: string]\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "[abbrev: string, name: string]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "val abbrevNames = spark.createDataFrame(abbrevNamesRDD)\n",
    "abbrevNames.createOrReplaceTempView(\"abbrevs_to_names\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exercise 2: Try other SQL constructs\n",
    "\n",
    "Both using actual SQL and the API."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Apache Toree - Scala",
   "language": "scala",
   "name": "apache_toree_scala"
  },
  "language_info": {
   "codemirror_mode": "text/x-scala",
   "file_extension": ".scala",
   "mimetype": "text/x-scala",
   "name": "scala",
   "pygments_lexer": "scala",
   "version": "2.11.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
